package com.geqian.structure.db;

import com.geqian.structure.entity.NodeData;
import com.geqian.structure.pojo.DatabaseInfo;
import com.geqian.structure.entity.TableColumnInfo;
import com.geqian.structure.entity.TableInfo;
import com.geqian.structure.utils.UUIDUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author geqian
 * @date 20:49 2023/1/4
 */
public class JDBCUtils {


    private static DatabaseInfo databaseInfo;

    /**
     * 获取所有schema
     *
     * @return
     * @throws Exception
     */
    public static List<NodeData> getTableTree() {
        ResultSet resultSet = null;
        List<NodeData> schemaList = new ArrayList<>();
        try {
            final String sql = databaseInfo.getDatabases();
            final DatabaseColumn tableColumn = databaseInfo.getTableColumn();
            Connection connection = DbConnection.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String key = UUIDUtils.generateUUID();
                String schemaName = resultSet.getString(tableColumn.getSchemaName());
                NodeData databaseData = new NodeData();
                databaseData.setKey(key);
                databaseData.setValue(schemaName);
                List<NodeData> tables = getTables(schemaName, key);
                databaseData.setChildren(tables);
                schemaList.add(databaseData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (Objects.nonNull(resultSet)) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return schemaList;
    }


    /**
     * 获取指定schema下所有表名
     *
     * @param schemaName
     * @return
     * @throws Exception
     */
    public static List<NodeData> getTables(String schemaName, String parentKey) {
        ResultSet resultSet = null;
        List<NodeData> tableList = new ArrayList<>();
        try {
            final String sql = databaseInfo.getTables();
            final DatabaseColumn tableColumn = databaseInfo.getTableColumn();
            Connection connection = DbConnection.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, schemaName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                NodeData databaseData = new NodeData();
                databaseData.setKey(UUIDUtils.generateUUID());
                databaseData.setValue(resultSet.getString(tableColumn.getTableName()));
                databaseData.setSchema(schemaName);
                databaseData.setParentKey(parentKey);
                tableList.add(databaseData);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (Objects.nonNull(resultSet)) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableList;
    }

    /**
     * 获取指定schema下指定表信息
     *
     * @param schemaName
     * @return
     * @throws Exception
     */
    public static TableInfo getTableInfo(String schemaName, String tableName) {
        ResultSet resultSet = null;
        TableInfo tableInfo = new TableInfo();
        try {
            final String sql = databaseInfo.getTableInfo();
            final DatabaseColumn tableColumn = databaseInfo.getTableColumn();
            Connection connection = DbConnection.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, schemaName);
            preparedStatement.setString(2, tableName);
            resultSet = preparedStatement.executeQuery();
            tableInfo.setTableName(tableName);
            tableInfo.setTableSchema(schemaName);
            while (resultSet.next()) {
                String tableComment = resultSet.getString(tableColumn.getTableComment());
                tableInfo.setTableComment(tableComment);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (Objects.nonNull(resultSet)) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableInfo;
    }


    /**
     * 获取指定schema下指定表的详细信息
     *
     * @param schemaName
     * @param tableName
     * @return
     * @throws Exception
     */
    public static List<TableColumnInfo> getTableColumnInfoList(String schemaName, String tableName) {
        List<TableColumnInfo> columnDetailInfoList = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            final String sql = databaseInfo.getDetailedTableStructure();
            final DatabaseColumn tableColumn = databaseInfo.getTableColumn();
            Connection connection = DbConnection.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, schemaName);
            preparedStatement.setString(2, tableName);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String columnName = resultSet.getString(tableColumn.getColumnName());
                String columnType = resultSet.getString(tableColumn.getColumnType());
                String columnComment = resultSet.getString(tableColumn.getColumnComment() == null ? "" : tableColumn.getColumnComment());
                String isNullable = resultSet.getString(tableColumn.getIsNullable());
                String columnDefault = resultSet.getString(tableColumn.getColumnDefault());
                TableColumnInfo columnDetailInfo = new TableColumnInfo(columnName, columnType, columnComment, isNullable, columnDefault);
                columnDetailInfoList.add(columnDetailInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (Objects.nonNull(resultSet)) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnDetailInfoList;
    }

    public static void setDatabaseInfo(DatabaseInfo databaseInfo) {
        JDBCUtils.databaseInfo = databaseInfo;
    }
}
